package userPages;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.LinkedList;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import database.DBUtils;

/**
 * Servlet implementation class SearchReservation
 */
public class SearchReservation extends HttpServlet {
	private static final long serialVersionUID = 1L;

	/**
	 * @see HttpServlet#HttpServlet()
	 */
	public SearchReservation() {
		super();
		// TODO Auto-generated constructor stub
	}

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse
	 *      response)
	 */
	protected void doGet(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {

		String By = request.getParameter("By");

		response.setContentType("text/xml");

		if (By == null) {
			response
					.getWriter()
					.println(
							"<start><result>Please choose searc type!</result></start>");
			response.getWriter().flush();
			response.getWriter().close();
			return;
		}
		if (By.compareTo("0") == 0) {
			createFromToResponse(request, response);
		} else {
			createFromHoursResponse(request, response);

		}

		response.getWriter().flush();
		response.getWriter().close();
		return;

	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse
	 *      response)
	 */
	protected void doPost(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
	}

	/**
	 * Prepare FROM -TO response from DB
	 * @param response
	 * @param instrType
	 * @param startDate
	 * @param endDate
	 * @param username
	 * @param searchText
	 * @throws IOException
	 */
	private void prepareFromToRespone(HttpServletResponse response,
			String instrType, Date startDate, Date endDate, String username,
			String searchText) throws IOException {

		Connection conn = null;
		PreparedStatement selectUserStmt = null;
		ResultSet rs = null;
		try {
			conn = DBUtils.getConnection();
			String DATE_FORMAT_NOW = "yyyy-MM-dd HH:mm:ss";
			SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT_NOW);
			String strEndDate = sdf.format(endDate);
			String strStartDate = sdf.format(startDate);
			boolean isNoSearch = false;
			// System.out.println("'" + searchText + "'");

			if (searchText == null) {
				// System.out.println("null!");
				isNoSearch = true;
			} else {
				if (searchText.trim().isEmpty()) {
					isNoSearch = true;
				}
			}
			//System.out.println(isNoSearch);
			if (isNoSearch) {

				String query = "select i.id, i.name, it.name as type "
						+ "from instruments as i LEFT OUTER JOIN reservations as r ON ( r.instrumentid = i.id), instrumenttypes as it "
						+ "WHERE i.isActive = 1 && ";
				if (instrType.compareTo("0") != 0) {
					query += " i.typeid='" + instrType + "' && ";
				}
				query += "i.typeID = it.id && "
						+ "NOT EXISTS( SELECT * FROM reservations as r2 where r.instrumentid = r2.instrumentid && "
						+ "endTime >  ? && starttime < ?) && "
						+ " EXISTS( SELECT * FROM permissions as p WHERE userLogin = ? && p.typeID=i.typeID) "
						+ "GROUP BY i.id;";

				// System.out.println(query);
				// System.out.println(strStartDate);
				// System.out.println(strEndDate);
				// System.out.println(username);
				selectUserStmt = conn.prepareStatement(query);
				selectUserStmt.setString(1, strStartDate);
				selectUserStmt.setString(2, strEndDate);
				selectUserStmt.setString(3, username);
				rs = selectUserStmt.executeQuery();
				response.getWriter().println("<start>");

				if (printFromToResponse(response, rs, endDate, startDate, null)) {
					response.getWriter().println("<result>OK</result>");
				} else {
					response
							.getWriter()
							.println(
									"<result>Result is empty, please, enter another requirements</result>");
				}

				response.getWriter().print("</start>");
			} else {
				LinkedList<String> ids = new LinkedList<String>();
				// Search by instrument name
				String query = "select i.id, i.name, it.name as type "
						+ "from instruments as i LEFT OUTER JOIN reservations as r ON ( r.instrumentid = i.id), instrumenttypes as it "
						+ "WHERE i.isActive = 1 && ";
				if (instrType.compareTo("0") != 0) {
					query += " i.typeid='" + instrType + "' && ";
				}

				query += "match (i.name) against ('" + searchText
						+ "' IN BOOLEAN MODE) && ";

				query += "i.typeID = it.id && "
						+ "NOT EXISTS( SELECT * FROM reservations as r2 where r.instrumentid = r2.instrumentid && "
						+ "endTime >  ? && starttime < ?) && "
						+ " EXISTS( SELECT * FROM permissions as p WHERE userLogin = ? && p.typeID=i.typeID) "
						+ "GROUP BY i.id;";

				// System.out.println(query);
				// System.out.println(strStartDate);
				// System.out.println(strEndDate);
				// System.out.println(username);
				selectUserStmt = conn.prepareStatement(query);
				selectUserStmt.setString(1, strStartDate);
				selectUserStmt.setString(2, strEndDate);
				selectUserStmt.setString(3, username);
				rs = selectUserStmt.executeQuery();
				response.getWriter().println("<start>");

				printFromToResponse(response, rs, endDate, startDate, ids);
				// response.getWriter().println("<result>OK</result>");
				// }
				// Search by instrument type
				query = "select i.id, i.name, it.name as type "
						+ "from instruments as i LEFT OUTER JOIN reservations as r ON ( r.instrumentid = i.id), instrumenttypes as it "
						+ "WHERE i.isActive = 1 && ";
				if (instrType.compareTo("0") != 0) {
					query += " i.typeid='" + instrType + "' && ";
				} else {

					query += "(";
					String[] splited = searchText.split(" ");
					for (int i = 0; i < splited.length; i++) {
						query += "it.name = '" + splited[i] + "' ";
						if (i + 1 < splited.length)
							query += "|| ";
					}
					query += ") && ";

				}
				query += "i.typeID = it.id && "
						+ "NOT EXISTS( SELECT * FROM reservations as r2 where r.instrumentid = r2.instrumentid && "
						+ "endTime >  ? && starttime < ?) && "
						+ " EXISTS( SELECT * FROM permissions as p WHERE userLogin = ? && p.typeID=i.typeID) "
						+ "GROUP BY i.id;";

				// System.out.println(query);
				// System.out.println(strStartDate);
				// System.out.println(strEndDate);
				// System.out.println(username);
				selectUserStmt = conn.prepareStatement(query);
				selectUserStmt.setString(1, strStartDate);
				selectUserStmt.setString(2, strEndDate);
				selectUserStmt.setString(3, username);
				rs = selectUserStmt.executeQuery();

				printFromToResponse(response, rs, endDate, startDate, ids);
				// Search by instrument description
				query = "select i.id, i.name, it.name as type "
						+ "from instruments as i LEFT OUTER JOIN reservations as r ON ( r.instrumentid = i.id), instrumenttypes as it "
						+ "WHERE i.isActive = 1 && ";
				if (instrType.compareTo("0") != 0) {
					query += " i.typeid='" + instrType + "' && ";
				}

				query += "match (i.description) against ('" + searchText
						+ "') && ";

				query += "i.typeID = it.id && "
						+ "NOT EXISTS( SELECT * FROM reservations as r2 where r.instrumentid = r2.instrumentid && "
						+ "endTime >  ? && starttime < ?) && "
						+ " EXISTS( SELECT * FROM permissions as p WHERE userLogin = ? && p.typeID=i.typeID) "
						+ "GROUP BY i.id;";

				//System.out.println(query);
				// System.out.println(strStartDate);
				// System.out.println(strEndDate);
				// System.out.println(username);
				selectUserStmt = conn.prepareStatement(query);
				selectUserStmt.setString(1, strStartDate);
				selectUserStmt.setString(2, strEndDate);
				selectUserStmt.setString(3, username);
				rs = selectUserStmt.executeQuery();

				printFromToResponse(response, rs, endDate, startDate, ids);

				if (ids.isEmpty()) {
					// Search by instrument description
					query = "select i.id, i.name, it.name as type "
							+ "from instruments as i LEFT OUTER JOIN reservations as r ON ( r.instrumentid = i.id), instrumenttypes as it "
							+ "WHERE i.isActive = 1 && ";
					if (instrType.compareTo("0") != 0) {
						query += " i.typeid='" + instrType + "' && ";
					}

					query += "match (i.description) against ('" + searchText
							+ "' IN BOOLEAN MODE) && ";

					query += "i.typeID = it.id && "
							+ "NOT EXISTS( SELECT * FROM reservations as r2 where r.instrumentid = r2.instrumentid && "
							+ "endTime >  ? && starttime < ?) && "
							+ " EXISTS( SELECT * FROM permissions as p WHERE userLogin = ? && p.typeID=i.typeID) "
							+ "GROUP BY i.id;";

					//System.out.println(query);
					// System.out.println(strStartDate);
					// System.out.println(strEndDate);
					// System.out.println(username);
					selectUserStmt = conn.prepareStatement(query);
					selectUserStmt.setString(1, strStartDate);
					selectUserStmt.setString(2, strEndDate);
					selectUserStmt.setString(3, username);
					rs = selectUserStmt.executeQuery();

					printFromToResponse(response, rs, endDate, startDate, ids);
					if (ids.isEmpty()) {
						response
								.getWriter()
								.println(
										"<result>Result is empty, please, enter another requirements</result>");
					} else {
						response.getWriter().println("<result>OK</result>");
					}

				} else {
					response.getWriter().println("<result>OK</result>");

				}

				response.getWriter().println("</start>");

			}

		} catch (Exception e) {
			response
					.getWriter()
					.println(
							"<start><result>Sorry, due to DB problems, cannot make request now </result></start>");

		} finally {
			DBUtils.close(rs);
			DBUtils.close(selectUserStmt);
			DBUtils.close(conn);
		}

	}

	/**
	 * Print from result from db request to output FROM - TO response
	 * @param response
	 * @param rs
	 * @param endDate
	 * @param startDate
	 * @param ids
	 * @return
	 * @throws IOException
	 * @throws SQLException
	 */
	private boolean printFromToResponse(HttpServletResponse response,
			ResultSet rs, Date endDate, Date startDate, LinkedList<String> ids)
			throws IOException, SQLException {
		if (rs == null || !rs.next()) {
			return false;
		} else {
			//System.out.println("Good Response");
			// response.getWriter().print("<start><result>OK</result>");
			String DATE_FORMAT_NOW = "yyyy-MM-dd HH:mm";
			SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT_NOW);
			String strEndDate = sdf.format(endDate);
			String strStartDate = sdf.format(startDate);
			int minutes = (int) ((endDate.getTime() - startDate.getTime()) / (1000 * 60));
			int hours = minutes / 60;
			minutes = minutes % 60;
			do {
				if (ids != null) {
					if (ids.contains(rs.getString("id")))
						continue;
					else
						ids.add(rs.getString("id"));
				}
				String xml = "<line>" + "<id>" + rs.getString("id") + "</id>"
						+ "<name>" + rs.getString("name") + "</name>"
						+ "<type>" + rs.getString("type") + "</type>"
						+ "<stime>" + strStartDate + "</stime>" + "<etime>"
						+ strEndDate + "</etime>" + "<total>" + hours
						+ " Hours " + minutes + " Min</total>" + "</line>";

				response.getWriter().print(xml);

			} while (rs.next());
			// response.getWriter().print("</start>");
			return true;
		}
	}

	/**
	 * Prepare FROM  - TO response (Check parameters)
	 * @param request
	 * @param response
	 * @throws IOException
	 */
	private void createFromToResponse(HttpServletRequest request,
			HttpServletResponse response) throws IOException {
		String instrType = request.getParameter("type");
		String startTime = request.getParameter("startTime");
		String endTime = request.getParameter("endTime");
		String searchText = request.getParameter("search");

		Date startDate, endDate = null;

		DateFormat formatter = new SimpleDateFormat("dd/MM/yy_HH:mm");

		if (instrType == null) {
			response.getWriter().println(
					"<start><result>Type is Null</result></start>");
			return;
		}
		if (startTime == null) {
			response.getWriter().println(
					"<start><result>Start Time is Null</result></start>");
			return;
		}

		if (endTime == null) {
			response.getWriter().println(
					"<start><result>End Time is Null</result></start>");
			return;
		}

		try {
			endDate = formatter.parse(endTime);
		} catch (ParseException e) {
			response
					.getWriter()
					.print(
							"<start><result>Please, enter End Time and Date correctly</result></start>");
			return;
		}

		try {
			startDate = formatter.parse(startTime);
		} catch (ParseException e) {
			response
					.getWriter()
					.print(
							"<start><result>Please, enter Start Time and Date correctly</result></start>");
			return;
		}

		if (!startDate.before(endDate)) {
			response
					.getWriter()
					.print(
							"<start><result>Please, enter Start Time before End Time</result></start>");
			return;
		}

		if (startDate.before(Calendar.getInstance().getTime())) {
			response
					.getWriter()
					.print(
							"<start><result>You are trying make reservation in a past!</result></start>");
			return;

		}

		prepareFromToRespone(response, instrType, startDate, endDate, request
				.getUserPrincipal().getName(), searchText);

		response.getWriter().flush();
		response.getWriter().close();
		return;

	}

	/**
	 * Prepare FROM + HOURS response (Check parameters)
	 * @param request
	 * @param response
	 * @throws IOException
	 */
	private void createFromHoursResponse(HttpServletRequest request,
			HttpServletResponse response) throws IOException {

		String instrType = request.getParameter("type");
		String startTime = request.getParameter("startTime");
		String hours = request.getParameter("Hours");
		String minutes = request.getParameter("Minutes");
		String searchText = request.getParameter("search");

		Date startDate = null;
		int hr, min;

		DateFormat formatter = new SimpleDateFormat("dd/MM/yy_HH:mm");

		if (instrType == null) {
			response.getWriter().println(
					"<start><result>Type is Null</result></start>");
			return;
		}
		if (startTime == null) {
			response.getWriter().println(
					"<start><result>Start Time is Null</result></start>");
			return;
		}

		try {
			startDate = formatter.parse(startTime);
		} catch (ParseException e) {
			response
					.getWriter()
					.print(
							"<start><result>Please, enter Start Time and Date correctly</result></start>");
			return;
		}

		if (hours == null) {
			response.getWriter().println(
					"<start><result>Hours is Null</result></start>");
			return;
		}
		if (minutes == null) {

			response.getWriter().println(
					"<start><result>Minutes is Null</result></start>");
			return;
		}

		try {
			hr = Integer.parseInt(hours);
			min = Integer.parseInt(minutes);
		} catch (Exception e) {
			response.getWriter().println(
					"<start><result>Hours are not number!</result></start>");
			return;
		}

		if (hr < 0 || hr > 1000) {
			response
					.getWriter()
					.println(
							"<start><result>Hours can be negative number!</result></start>");
			return;
		}

		if (min < 0 || min > 60 || min % 15 != 0) {
			response
					.getWriter()
					.println(
							"<start><result>Minutes: only 0, 15, 30 or 45 can be used</result></start>");
			return;
		}
		if(hr == 0 && min == 0)
		{
			response
			.getWriter()
			.println(
					"<start><result>Impossible to request instrument for zero time</result></start>");
			return;
		}

		if (startDate.before(Calendar.getInstance().getTime())) {
			response
					.getWriter()
					.print(
							"<start><result>You are trying make reservation in a past!</result></start>");
			return;

		}

		//System.out.println("Time: " + hr + " " + min);
		prepareFromHoursResponse(response, instrType, startDate, hr, min,
				request.getUserPrincipal().getName(), searchText);

		response.getWriter().flush();
		response.getWriter().close();
		return;
	}

	/**
	 * FROM + HOURS response: from DB
	 * @param response
	 * @param instrType
	 * @param startDate
	 * @param hr
	 * @param min
	 * @param username
	 * @param searchText
	 * @throws IOException
	 */
	private void prepareFromHoursResponse(HttpServletResponse response,
			String instrType, Date startDate, int hr, int min, String username,
			String searchText) throws IOException {
		Connection conn = null;
		PreparedStatement selectUserStmt = null;
		ResultSet rs = null;
		try {

			response.getWriter().print("<start>");
			LinkedList<String> ids = new LinkedList<String>();
			conn = DBUtils.getConnection();
			String DATE_FORMAT_NOW = "yyyy-MM-dd HH:mm:ss";
			SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT_NOW);
			Calendar cal = Calendar.getInstance();
			cal.setTime(startDate);
			cal.add(Calendar.MINUTE, hr * 60 + min);
			String strEndDate = sdf.format(cal.getTime());
			String strStartDate = sdf.format(startDate);

			boolean isNoSearch = false;

			if (searchText == null) {
				// System.out.println("null!");
				isNoSearch = true;
			} else {
				if (searchText.trim().isEmpty()) {
					isNoSearch = true;
				}
			}

			if (isNoSearch) {
				String query = "select i.id, i.name, it.name as type "
						+ "from instruments as i LEFT OUTER JOIN reservations as r ON ( r.instrumentid = i.id), instrumenttypes as it "
						+ "WHERE i.isActive = 1 && ";
				if (instrType.compareTo("0") != 0) {
					query += " i.typeid='" + instrType + "' && ";
				}
				query += "i.typeID = it.id && "
						+ "NOT EXISTS( SELECT * FROM reservations as r2 where r.instrumentid = r2.instrumentid && "
						+ "endTime >  ? && starttime < ? ) && "
						+ " EXISTS( SELECT * FROM permissions as p WHERE userLogin = ? && p.typeID=i.typeID) "
						+ "GROUP BY i.id;";

				// System.out.println(query);
				// System.out.println(strStartDate);
				// System.out.println(strEndDate);
				// System.out.println(username);
				selectUserStmt = conn.prepareStatement(query);
				selectUserStmt.setString(1, strStartDate);
				selectUserStmt.setString(2, strEndDate);
				selectUserStmt.setString(3, username);
				rs = selectUserStmt.executeQuery();

				printFromHoursStrictTimeResponse(response, startDate, hr, min,
						rs, ids);

				query = "select i.id, i.name, it.name as type, MIN(r.endTime) as time "
						+ "from instruments as i LEFT OUTER JOIN reservations as r ON ( r.instrumentid = i.id ), instrumenttypes as it "
						+ "WHERE i.isActive = 1 &&";
				if (instrType.compareTo("0") != 0) {
					query += " i.typeid='" + instrType + "' && ";
				}
				query += "i.typeID = it.id && r.endtime > ? "
						+ "&& EXISTS( SELECT * FROM permissions as p WHERE userLogin = ? && p.typeID=i.typeID) "
						+ "&& NOT EXISTS( SELECT * FROM reservations as r2 WHERE r.instrumentid = r2.instrumentid "
						+ "&& r2.starttime < DATE_ADD(r.endtime, INTERVAL '"
						+ hr + ":" + min
						+ "' HOUR_MINUTE)  && r2.endtime > r.endtime ) "
						+ "GROUP BY i.id " + "Order by time;";

				// System.out.println(query);
				// System.out.println(strStartDate);
				// System.out.println(strEndDate);
				// System.out.println(username);
				selectUserStmt = conn.prepareStatement(query);
				selectUserStmt.setString(1, strStartDate);
				selectUserStmt.setString(2, username);
				// selectUserStmt.setInt(3, hr);
				// selectUserStmt.setInt(4, min);
				rs = selectUserStmt.executeQuery();
				printFromHoursAnyTimeResponse(response, hr, min, rs, ids);

				if (!ids.isEmpty())
					response.getWriter().print("<result>OK</result></start>");
				else {
					// System.out.println("Empty response");
					response
							.getWriter()
							.println(
									"<result>Result is empty, please, enter another requirements</result></start>");
					return;
				}
			} else {
				// ids = new LinkedList<String>();

				String query = "select i.id, i.name, it.name as type "
						+ "from instruments as i LEFT OUTER JOIN reservations as r ON ( r.instrumentid = i.id), instrumenttypes as it "
						+ "WHERE i.isActive = 1 && ";
				if (instrType.compareTo("0") != 0) {
					query += " i.typeid='" + instrType + "' && ";
				}
				query += "match (i.name) against ('" + searchText
						+ "' IN BOOLEAN MODE) && ";

				query += "i.typeID = it.id && "
						+ "NOT EXISTS( SELECT * FROM reservations as r2 where r.instrumentid = r2.instrumentid && "
						+ "endTime >  ? && starttime < ? ) && "
						+ " EXISTS( SELECT * FROM permissions as p WHERE userLogin = ? && p.typeID=i.typeID) "
						+ "GROUP BY i.id;";

				selectUserStmt = conn.prepareStatement(query);
				selectUserStmt.setString(1, strStartDate);
				selectUserStmt.setString(2, strEndDate);
				selectUserStmt.setString(3, username);
				rs = selectUserStmt.executeQuery();

				printFromHoursStrictTimeResponse(response, startDate, hr, min,
						rs, ids);

				query = "select i.id, i.name, it.name as type, MIN(r.endTime) as time "
						+ "from instruments as i LEFT OUTER JOIN reservations as r ON ( r.instrumentid = i.id ), instrumenttypes as it "
						+ "WHERE i.isActive = 1 &&";
				if (instrType.compareTo("0") != 0) {
					query += " i.typeid='" + instrType + "' && ";
				}

				query += "match (i.name) against ('" + searchText
						+ "' IN BOOLEAN MODE) && ";

				query += "i.typeID = it.id && r.endtime > ? "
						+ "&& EXISTS( SELECT * FROM permissions as p WHERE userLogin = ? && p.typeID=i.typeID) "
						+ "&& NOT EXISTS( SELECT * FROM reservations as r2 WHERE r.instrumentid = r2.instrumentid "
						+ "&& r2.starttime < DATE_ADD(r.endtime, INTERVAL '"
						+ hr + ":" + min
						+ "' HOUR_MINUTE)  && r2.endtime > r.endtime ) "
						+ "GROUP BY i.id " + "Order by time;";

				selectUserStmt = conn.prepareStatement(query);
				selectUserStmt.setString(1, strStartDate);
				selectUserStmt.setString(2, username);
				rs = selectUserStmt.executeQuery();
				printFromHoursAnyTimeResponse(response, hr, min, rs, ids);

				// Search by instrument type

				query = "select i.id, i.name, it.name as type "
						+ "from instruments as i LEFT OUTER JOIN reservations as r ON ( r.instrumentid = i.id), instrumenttypes as it "
						+ "WHERE i.isActive = 1 && ";
				if (instrType.compareTo("0") != 0) {
					query += " i.typeid='" + instrType + "' && ";
				} else {

					query += "(";
					String[] splited = searchText.split(" ");
					for (int i = 0; i < splited.length; i++) {
						query += "it.name = '" + splited[i] + "' ";
						if (i + 1 < splited.length)
							query += "|| ";
					}
					query += ") && ";

				}

				query += "i.typeID = it.id && "
						+ "NOT EXISTS( SELECT * FROM reservations as r2 where r.instrumentid = r2.instrumentid && "
						+ "endTime >  ? && starttime < ? ) && "
						+ " EXISTS( SELECT * FROM permissions as p WHERE userLogin = ? && p.typeID=i.typeID) "
						+ "GROUP BY i.id;";

				selectUserStmt = conn.prepareStatement(query);
				selectUserStmt.setString(1, strStartDate);
				selectUserStmt.setString(2, strEndDate);
				selectUserStmt.setString(3, username);
				rs = selectUserStmt.executeQuery();

				printFromHoursStrictTimeResponse(response, startDate, hr, min,
						rs, ids);

				query = "select i.id, i.name, it.name as type, MIN(r.endTime) as time "
						+ "from instruments as i LEFT OUTER JOIN reservations as r ON ( r.instrumentid = i.id ), instrumenttypes as it "
						+ "WHERE i.isActive = 1 &&";
				if (instrType.compareTo("0") != 0) {
					query += " i.typeid='" + instrType + "' && ";
				} else {

					query += "(";
					String[] splited = searchText.split(" ");
					for (int i = 0; i < splited.length; i++) {
						query += "it.name = '" + splited[i] + "' ";
						if (i + 1 < splited.length)
							query += "|| ";
					}
					query += ") && ";

				}

				query += "i.typeID = it.id && r.endtime > ? "
						+ "&& EXISTS( SELECT * FROM permissions as p WHERE userLogin = ? && p.typeID=i.typeID) "
						+ "&& NOT EXISTS( SELECT * FROM reservations as r2 WHERE r.instrumentid = r2.instrumentid "
						+ "&& r2.starttime < DATE_ADD(r.endtime, INTERVAL '"
						+ hr + ":" + min
						+ "' HOUR_MINUTE)  && r2.endtime > r.endtime ) "
						+ "GROUP BY i.id " + "Order by time;";

				selectUserStmt = conn.prepareStatement(query);
				selectUserStmt.setString(1, strStartDate);
				selectUserStmt.setString(2, username);
				rs = selectUserStmt.executeQuery();
				printFromHoursAnyTimeResponse(response, hr, min, rs, ids);

				// search by description
				query = "select i.id, i.name, it.name as type "
						+ "from instruments as i LEFT OUTER JOIN reservations as r ON ( r.instrumentid = i.id), instrumenttypes as it "
						+ "WHERE i.isActive = 1 && ";
				if (instrType.compareTo("0") != 0) {
					query += " i.typeid='" + instrType + "' && ";
				}

				query += "match (i.description) against ('" + searchText
						+ "') && ";

				query += "i.typeID = it.id && "
						+ "NOT EXISTS( SELECT * FROM reservations as r2 where r.instrumentid = r2.instrumentid && "
						+ "endTime >  ? && starttime < ? ) && "
						+ " EXISTS( SELECT * FROM permissions as p WHERE userLogin = ? && p.typeID=i.typeID) "
						+ "GROUP BY i.id;";

				selectUserStmt = conn.prepareStatement(query);
				selectUserStmt.setString(1, strStartDate);
				selectUserStmt.setString(2, strEndDate);
				selectUserStmt.setString(3, username);
				rs = selectUserStmt.executeQuery();

				printFromHoursStrictTimeResponse(response, startDate, hr, min,
						rs, ids);

				query = "select i.id, i.name, it.name as type, MIN(r.endTime) as time "
						+ "from instruments as i LEFT OUTER JOIN reservations as r ON ( r.instrumentid = i.id ), instrumenttypes as it "
						+ "WHERE i.isActive = 1 &&";
				if (instrType.compareTo("0") != 0) {
					query += " i.typeid='" + instrType + "' && ";
				}
				query += "match (i.description) against ('" + searchText
						+ "') && ";

				query += "i.typeID = it.id && r.endtime > ? "
						+ "&& EXISTS( SELECT * FROM permissions as p WHERE userLogin = ? && p.typeID=i.typeID) "
						+ "&& NOT EXISTS( SELECT * FROM reservations as r2 WHERE r.instrumentid = r2.instrumentid "
						+ "&& r2.starttime < DATE_ADD(r.endtime, INTERVAL '"
						+ hr + ":" + min
						+ "' HOUR_MINUTE)  && r2.endtime > r.endtime ) "
						+ "GROUP BY i.id " + "Order by time;";

				selectUserStmt = conn.prepareStatement(query);
				selectUserStmt.setString(1, strStartDate);
				selectUserStmt.setString(2, username);
				rs = selectUserStmt.executeQuery();
				printFromHoursAnyTimeResponse(response, hr, min, rs, ids);

				if (ids.isEmpty()) {
					query = "select i.id, i.name, it.name as type "
							+ "from instruments as i LEFT OUTER JOIN reservations as r ON ( r.instrumentid = i.id), instrumenttypes as it "
							+ "WHERE i.isActive = 1 && ";
					if (instrType.compareTo("0") != 0) {
						query += " i.typeid='" + instrType + "' && ";
					}

					query += "match (i.description) against ('" + searchText
							+ "' IN BOOLEAN MODE) && ";

					query += "i.typeID = it.id && "
							+ "NOT EXISTS( SELECT * FROM reservations as r2 where r.instrumentid = r2.instrumentid && "
							+ "endTime >  ? && starttime < ? ) && "
							+ " EXISTS( SELECT * FROM permissions as p WHERE userLogin = ? && p.typeID=i.typeID) "
							+ "GROUP BY i.id;";

					selectUserStmt = conn.prepareStatement(query);
					selectUserStmt.setString(1, strStartDate);
					selectUserStmt.setString(2, strEndDate);
					selectUserStmt.setString(3, username);
					rs = selectUserStmt.executeQuery();

					printFromHoursStrictTimeResponse(response, startDate, hr,
							min, rs, ids);

					query = "select i.id, i.name, it.name as type, MIN(r.endTime) as time "
							+ "from instruments as i LEFT OUTER JOIN reservations as r ON ( r.instrumentid = i.id ), instrumenttypes as it "
							+ "WHERE i.isActive = 1 &&";
					if (instrType.compareTo("0") != 0) {
						query += " i.typeid='" + instrType + "' && ";
					}
					query += "match (i.description) against ('" + searchText
							+ "' IN BOOLEAN MODE) && ";

					query += "i.typeID = it.id && r.endtime > ? "
							+ "&& EXISTS( SELECT * FROM permissions as p WHERE userLogin = ? && p.typeID=i.typeID) "
							+ "&& NOT EXISTS( SELECT * FROM reservations as r2 WHERE r.instrumentid = r2.instrumentid "
							+ "&& r2.starttime < DATE_ADD(r.endtime, INTERVAL '"
							+ hr + ":" + min
							+ "' HOUR_MINUTE)  && r2.endtime > r.endtime ) "
							+ "GROUP BY i.id " + "Order by time;";

					selectUserStmt = conn.prepareStatement(query);
					selectUserStmt.setString(1, strStartDate);
					selectUserStmt.setString(2, username);
					rs = selectUserStmt.executeQuery();
					printFromHoursAnyTimeResponse(response, hr, min, rs, ids);

					if (ids.isEmpty()) {
						response
								.getWriter()
								.println(
										"<result>Result is empty, please, enter another requirements</result>");
					} else {
						response.getWriter().println("<result>OK</result>");
					}

				} else {
					response.getWriter().println("<result>OK</result>");

				}

				response.getWriter().println("</start>");
			}
		} catch (Exception e) {
			// System.out.println(e.getMessage());
			response
					.getWriter()
					.println(
							"<result>Sorry, due to DB problems, cannot make request now </result></start>");

		} finally {
			DBUtils.close(rs);
			DBUtils.close(selectUserStmt);
			DBUtils.close(conn);
		}
	}

	/**
	 * FROM + HOURS response: print free time response
	 * @param response
	 * @param hr
	 * @param min
	 * @param rs
	 * @param ids
	 * @return
	 * @throws SQLException
	 * @throws ParseException
	 * @throws IOException
	 */
	private boolean printFromHoursAnyTimeResponse(HttpServletResponse response,
			int hr, int min, ResultSet rs, LinkedList<String> ids)
			throws SQLException, ParseException, IOException {
		if (rs == null || !rs.next()) {

			return false;
		} else {

			String DATE_FORMAT_NOW = "yyyy-MM-dd HH:mm";
			SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT_NOW);

			do {
				Date start = sdf.parse(rs.getString("time"));
				String strStartDate = sdf.format(start);

				Calendar cal = Calendar.getInstance();
				cal.setTime(start);
				cal.add(Calendar.MINUTE, hr * 60 + min);

				String strEndDate = sdf.format(cal.getTime());

				if (!ids.contains(rs.getString("id"))) {
					ids.add(rs.getString("id"));
					// strStartDate = sdf.format(rs.getString("time"));
					String xml = "<line>" + "<id>" + rs.getString("id")
							+ "</id>" + "<name>" + rs.getString("name")
							+ "</name>" + "<type>" + rs.getString("type")
							+ "</type>" + "<stime>" + strStartDate + "</stime>"
							+ "<etime>" + strEndDate + "</etime>" + "<total>"
							+ hr + " Hours " + min + " Min</total>" + "</line>";

					// System.out.println(xml);

					response.getWriter().print(xml);
				}

			} while (rs.next());
			return true;
		}
	}

	/**
	 * FROM + HOURS response: print known time response
	 * @param response
	 * @param startDate
	 * @param hr
	 * @param min
	 * @param rs
	 * @param ids
	 * @return
	 * @throws SQLException
	 * @throws IOException
	 */
	private boolean printFromHoursStrictTimeResponse(
			HttpServletResponse response, Date startDate, int hr, int min,
			ResultSet rs, LinkedList<String> ids) throws SQLException,
			IOException {
		if (rs == null || !rs.next()) {

			return false;
		} else {
			// System.out.println("Good Response 1");

			String DATE_FORMAT_NOW = "yyyy-MM-dd HH:mm";
			SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT_NOW);
			Calendar cal = Calendar.getInstance();
			cal.setTime(startDate);
			cal.add(Calendar.MINUTE, hr * 60 + min);

			String strEndDate = sdf.format(cal.getTime());
			String strStartDate = sdf.format(startDate);
			int minutes = (int) ((cal.getTime().getTime() - startDate.getTime()) / (1000 * 60));
			int hours = minutes / 60;
			minutes = minutes % 60;
			do {
				if (!ids.contains(rs.getString("id"))) {
					String xml = "<line>" + "<id>" + rs.getString("id")
							+ "</id>" + "<name>" + rs.getString("name")
							+ "</name>" + "<type>" + rs.getString("type")
							+ "</type>" + "<stime>" + strStartDate + "</stime>"
							+ "<etime>" + strEndDate + "</etime>" + "<total>"
							+ hours + " Hours " + minutes + " Min</total>"
							+ "</line>";

					response.getWriter().print(xml);
					ids.add(rs.getString("id"));
				}

			} while (rs.next());
			return true;

		}
	}
}
